﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.Common;
using System.Data.OracleClient;
using System.Text.RegularExpressions;
using System.Globalization;

namespace TestMethod
{
    public class OracleTT
    {
        const string Namespace = "DataGenerator";
        const string ConnectionStringName = "DataGeneratorTable";
        const string ConnectionString = @"Data Source=test2;user id=enq; password=password";

        //this is a list of tables you don't want generated
        string[] ExcludeTables = new string[]{
    "sysdiagrams",
    "BuildVersion",
    };

        string KeyColumnsValues(string tableName, IEnumerable<Column> keyColumns)
        {
            return string.Join(",", keyColumns.Select(key => string.Format("{0}.{1}", tableName.ToLower(), key.CleanName)).ToArray());
        }
        bool IsKeyColumnsRequire(Column identityCol, IEnumerable<Column> keyColumns)
        {
            if (identityCol == null && keyColumns.Count() > 0)
            {
                return true;
            }
            else if (identityCol != null && keyColumns.Contains(identityCol) && keyColumns.Count() == 1)
            {
                return true;
            }
            return false;
        }


        IDataReader GetReader(string sql)
        {

            OracleConnection conn = new OracleConnection(ConnectionString);
            OracleCommand cmd = new OracleCommand(sql, conn);
            conn.Open();
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        string CleanUp(string tableName)
        {
            string result = tableName;

            //strip blanks
            result = result.Replace(" ", "");

            //put your logic here...

            return result;
        }
        string CheckNullable(Column col)
        {
            string result = "";
            if (col.IsNullable && col.SysType != "byte[]" && col.SysType != "string")
                result = "?";
            return result;
        }
        OracleCommand GetCommand(string sql)
        {
            OracleConnection conn = new OracleConnection(ConnectionString);
            OracleCommand cmd = new OracleCommand(sql, conn);
            conn.Open();
            return cmd;
        }

        const string FKSql = @"select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name =:table_name";


        const string TABLE_SQL = @"select * from user_tables ";

        const string COLUMN_SQL = @"select Table_Name , COLUMN_NAME ColumnName ,DATA_TYPE DataType, DATA_LENGTH MaxLength  from user_tab_columns where Table_Name=:Table_Name";






      public  List<Table> LoadTables()
        {
            var result = new List<Table>();

            //pull the tables in a reader
            using (IDataReader rdr = GetReader(TABLE_SQL))
            {
                while (rdr.Read())
                {
                    Table tbl = new Table();
                    tbl.Name = rdr["TABLE_NAME"].ToString();
                    //tbl.Schema = rdr["TABLE_SCHEMA"].ToString();
                    tbl.Columns = LoadColumns(tbl);
                    tbl.PrimaryKey = GetPK(tbl.Name);
                    tbl.CleanName = CleanUp(tbl.Name);
                    tbl.ClassName = Inflector.MakeSingular(tbl.CleanName);
                    tbl.QueryableName = Inflector.MakePlural(tbl.ClassName);

                    //set the PK for the columns
                    var pkColumns = tbl.Columns.Where(x => tbl.PrimaryKey.Contains(x.Name.ToLower().Trim()));
                    foreach (var col in pkColumns)
                    {
                        col.IsPK = true;
                    }
                    //tbl.FKTables = LoadFKTables(tbl.Name);

                    result.Add(tbl);
                }
            }

            foreach (Table tbl in result)
            {
                //loop the FK tables and see if there's a match for our FK columns
                //foreach (Column col in tbl.Columns)
                //{
                //    col.IsForeignKey = tbl.FKTables.Any(
                //        x => x.ThisColumn.Equals(col.Name, StringComparison.InvariantCultureIgnoreCase)
                //    );
                //}
            }
            return result;
        }

        List<Column> LoadColumns(Table tbl)
        {
            var result = new List<Column>();
            var cmd = GetCommand(COLUMN_SQL);
            cmd.Parameters.AddWithValue(":Table_Name", tbl.Name);

            using (IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                while (rdr.Read())
                {
                    Column col = new Column();
                    col.Name = rdr["ColumnName"].ToString();
                    col.CleanName = CleanUp(col.Name);
                    col.DataType = rdr["DataType"].ToString();
                    col.SysType = GetSysType(col.DataType);
                    col.DbType = GetDbType(col.DataType);
                    //col.AutoIncrement = rdr["IsIdentity"].ToString() == "1";
                    //col.IsNullable = rdr["IsNullable"].ToString() == "YES";
                    int.TryParse(rdr["MaxLength"].ToString(), out col.MaxLength);

                    result.Add(col);
                }

            }

            return result;
        }

        List<FKTable> LoadFKTables(string tableName)
        {

            //this is a "bi-directional" scheme
            //which pulls both 1-many and many-1

            var result = new List<FKTable>();
            var cmd = GetCommand(FKSql);
            cmd.Parameters.AddWithValue("@tableName", tableName);
            using (IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                while (rdr.Read())
                {
                    FKTable fk = new FKTable();
                    string thisTable = rdr["ThisTable"].ToString();

                    if (tableName.ToLower() == thisTable.ToLower())
                    {
                        fk.ThisTable = rdr["ThisTable"].ToString();
                        fk.ThisColumn = rdr["ThisColumn"].ToString();
                        fk.OtherTable = rdr["OtherTable"].ToString();
                        fk.OtherColumn = rdr["OtherColumn"].ToString();

                    }
                    else
                    {
                        fk.ThisTable = rdr["OtherTable"].ToString();
                        fk.ThisColumn = rdr["OtherColumn"].ToString();
                        fk.OtherTable = rdr["ThisTable"].ToString();
                        fk.OtherColumn = rdr["ThisColumn"].ToString();

                    }

                    fk.OtherClass = Inflector.MakeSingular(CleanUp(fk.OtherTable));
                    fk.OtherQueryable = Inflector.MakePlural(fk.OtherClass);

                    result.Add(fk);
                }
            }
            return result;

        }

        List<string> GetPK(string table)
        {

            DataTable pkTable = new DataTable();
            string sql = @"select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name =:tableName";

            var cmd = GetCommand(sql);
            cmd.Parameters.AddWithValue(":tableName", table);
            var primaryKeys = new List<string>();
            using (IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                while (rdr.Read())
                {
                    var key = rdr["COLUMN_NAME"].ToString().ToLower();
                    primaryKeys.Add(key);
                }
            }

            return primaryKeys;
        }

        string GetSysType(string sqlType)
        {
            string sysType = "string";
            switch (sqlType)
            {
                case "bigint":
                    sysType = "long";
                    break;
                case "smallint":
                    sysType = "short";
                    break;
                case "int":
                    sysType = "int";
                    break;
                case "uniqueidentifier":
                    sysType = "Guid";
                    break;
                case "smalldatetime":
                case "datetime":
                    sysType = "DateTime";
                    break;
                case "float":
                    sysType = "double";
                    break;
                case "real":
                case "numeric":
                case "smallmoney":
                case "decimal":
                case "money":
                    sysType = "decimal";
                    break;
                case "tinyint":
                case "bit":
                    sysType = "bool";
                    break;
                case "image":
                case "binary":
                case "varbinary":
                    sysType = "byte[]";
                    break;
            }
            return sysType;
        }
        DbType GetDbType(string sqlType)
        {
            switch (sqlType)
            {
                case "varchar":
                    return DbType.AnsiString;
                case "nvarchar":
                    return DbType.String;
                case "int":
                    return DbType.Int32;
                case "uniqueidentifier":
                    return DbType.Guid;
                case "datetime":
                    return DbType.DateTime;
                case "bigint":
                    return DbType.Int64;
                case "binary":
                    return DbType.Binary;
                case "bit":
                    return DbType.Boolean;
                case "char":
                    return DbType.AnsiStringFixedLength;
                case "decimal":
                    return DbType.Decimal;
                case "float":
                    return DbType.Double;
                case "image":
                    return DbType.Binary;
                case "money":
                    return DbType.Currency;
                case "nchar":
                    return DbType.String;
                case "ntext":
                    return DbType.String;
                case "numeric":
                    return DbType.Decimal;
                case "real":
                    return DbType.Single;
                case "smalldatetime":
                    return DbType.DateTime;
                case "smallint":
                    return DbType.Int16;
                case "smallmoney":
                    return DbType.Currency;
                case "sql_variant":
                    return DbType.String;
                case "sysname":
                    return DbType.String;
                case "text":
                    return DbType.AnsiString;
                case "timestamp":
                    return DbType.Binary;
                case "tinyint":
                    return DbType.Byte;
                case "varbinary":
                    return DbType.Binary;
                case "xml":
                    return DbType.Xml;
                default:
                    return DbType.AnsiString;
            }


        }

        public class Table
        {

            public List<Column> Columns;
            public List<FKTable> FKTables;
            public string Name;
            public string CleanName;
            public string ClassName;
            public List<string> PrimaryKey;
            public string Schema;
            public string QueryableName;

            public bool HasLogicalDelete()
            {
                return this.Columns.Any(x => x.Name.ToLower() == "deleted" || x.Name.ToLower() == "isdeleted");
            }
            public Column DeleteColumn
            {
                get
                {
                    Column result = null;
                    if (this.Columns.Any(x => x.Name.ToLower() == "deleted"))
                        result = this.Columns.Single(x => x.Name.ToLower() == "deleted");
                    if (this.Columns.Any(x => x.Name.ToLower() == "isdeleted"))
                        result = this.Columns.Single(x => x.Name.ToLower() == "isdeleted");
                    return result;
                }
            }
            public Column PK
            {
                get
                {
                    return this.Columns.SingleOrDefault(x => x.IsPK) ?? this.Columns[0];
                }
            }
            public Column Descriptor
            {
                get
                {
                    if (this.Columns.Count == 1)
                    {
                        return this.Columns[0];
                    }
                    else
                    {
                        //get the first string column
                        Column result = null;
                        result = this.Columns.FirstOrDefault(x => x.SysType.ToLower().Trim() == "string");
                        if (result == null)
                            result = this.Columns[1];
                        return result;
                    }
                }
            }
        }

        public class Column
        {
            public string Name;
            public string CleanName;
            public string SysType;
            public string DataType;
            public DbType DbType;
            public bool AutoIncrement;
            public bool IsPK;
            public int MaxLength;
            public bool IsNullable;
            public bool IsForeignKey;
        }
        public class FKTable
        {
            public string ThisTable;
            public string ThisColumn;
            public string OtherTable;
            public string OtherColumn;
            public string OtherClass;
            public string OtherQueryable;
        }

        public class SP
        {
            public string Name;
            public string CleanName;
            public string ClassName;
            public List<SPParam> Parameters;
            public SP()
            {
                Parameters = new List<SPParam>();
            }
            public string ArgList
            {
                get
                {
                    StringBuilder sb = new StringBuilder();
                    int loopCount = 1;
                    foreach (var par in Parameters)
                    {
                        sb.AppendFormat("{0} {1}", par.SysType, par.CleanName);
                        if (loopCount < Parameters.Count)
                            sb.Append(",");
                        loopCount++;
                    }
                    return sb.ToString();
                }
            }
        }
        public class SPParam
        {
            public string Name;
            public string CleanName;
            public string SysType;
            public string DbType;
        }

        /*
         * SubSonic - http://subsonicproject.com
         * 
         * The contents of this file are subject to the New BSD
         * License (the "License"); you may not use this file
         * except in compliance with the License. You may obtain a copy of
         * the License at http://www.opensource.org/licenses/bsd-license.php
         * 
         * Software distributed under the License is distributed on an 
         * "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
         * implied. See the License for the specific language governing
         * rights and limitations under the License.
        */

        /// <summary>
        /// Summary for the Inflector class
        /// </summary>
        public static class Inflector
        {
            private static readonly List<InflectorRule> _plurals = new List<InflectorRule>();
            private static readonly List<InflectorRule> _singulars = new List<InflectorRule>();
            private static readonly List<string> _uncountables = new List<string>();

            /// <summary>
            /// Initializes the <see cref="Inflector"/> class.
            /// </summary>
            static Inflector()
            {
                AddPluralRule("$", "s");
                AddPluralRule("s$", "s");
                AddPluralRule("(ax|test)is$", "$1es");
                AddPluralRule("(octop|vir)us$", "$1i");
                AddPluralRule("(alias|status)$", "$1es");
                AddPluralRule("(bu)s$", "$1ses");
                AddPluralRule("(buffal|tomat)o$", "$1oes");
                AddPluralRule("([ti])um$", "$1a");
                AddPluralRule("sis$", "ses");
                AddPluralRule("(?:([^f])fe|([lr])f)$", "$1$2ves");
                AddPluralRule("(hive)$", "$1s");
                AddPluralRule("([^aeiouy]|qu)y$", "$1ies");
                AddPluralRule("(x|ch|ss|sh)$", "$1es");
                AddPluralRule("(matr|vert|ind)ix|ex$", "$1ices");
                AddPluralRule("([m|l])ouse$", "$1ice");
                AddPluralRule("^(ox)$", "$1en");
                AddPluralRule("(quiz)$", "$1zes");

                AddSingularRule("s$", String.Empty);
                AddSingularRule("ss$", "ss");
                AddSingularRule("(n)ews$", "$1ews");
                AddSingularRule("([ti])a$", "$1um");
                AddSingularRule("((a)naly|(b)a|(d)iagno|(p)arenthe|(p)rogno|(s)ynop|(t)he)ses$", "$1$2sis");
                AddSingularRule("(^analy)ses$", "$1sis");
                AddSingularRule("([^f])ves$", "$1fe");
                AddSingularRule("(hive)s$", "$1");
                AddSingularRule("(tive)s$", "$1");
                AddSingularRule("([lr])ves$", "$1f");
                AddSingularRule("([^aeiouy]|qu)ies$", "$1y");
                AddSingularRule("(s)eries$", "$1eries");
                AddSingularRule("(m)ovies$", "$1ovie");
                AddSingularRule("(x|ch|ss|sh)es$", "$1");
                AddSingularRule("([m|l])ice$", "$1ouse");
                AddSingularRule("(bus)es$", "$1");
                AddSingularRule("(o)es$", "$1");
                AddSingularRule("(shoe)s$", "$1");
                AddSingularRule("(cris|ax|test)es$", "$1is");
                AddSingularRule("(octop|vir)i$", "$1us");
                AddSingularRule("(alias|status)$", "$1");
                AddSingularRule("(alias|status)es$", "$1");
                AddSingularRule("^(ox)en", "$1");
                AddSingularRule("(vert|ind)ices$", "$1ex");
                AddSingularRule("(matr)ices$", "$1ix");
                AddSingularRule("(quiz)zes$", "$1");

                AddIrregularRule("person", "people");
                AddIrregularRule("man", "men");
                AddIrregularRule("child", "children");
                AddIrregularRule("sex", "sexes");
                AddIrregularRule("tax", "taxes");
                AddIrregularRule("move", "moves");

                AddUnknownCountRule("equipment");
                AddUnknownCountRule("information");
                AddUnknownCountRule("rice");
                AddUnknownCountRule("money");
                AddUnknownCountRule("species");
                AddUnknownCountRule("series");
                AddUnknownCountRule("fish");
                AddUnknownCountRule("sheep");
            }

            /// <summary>
            /// Adds the irregular rule.
            /// </summary>
            /// <param name="singular">The singular.</param>
            /// <param name="plural">The plural.</param>
            private static void AddIrregularRule(string singular, string plural)
            {
                AddPluralRule(String.Concat("(", singular[0], ")", singular.Substring(1), "$"), String.Concat("$1", plural.Substring(1)));
                AddSingularRule(String.Concat("(", plural[0], ")", plural.Substring(1), "$"), String.Concat("$1", singular.Substring(1)));
            }

            /// <summary>
            /// Adds the unknown count rule.
            /// </summary>
            /// <param name="word">The word.</param>
            private static void AddUnknownCountRule(string word)
            {
                _uncountables.Add(word.ToLower());
            }

            /// <summary>
            /// Adds the plural rule.
            /// </summary>
            /// <param name="rule">The rule.</param>
            /// <param name="replacement">The replacement.</param>
            private static void AddPluralRule(string rule, string replacement)
            {
                _plurals.Add(new InflectorRule(rule, replacement));
            }

            /// <summary>
            /// Adds the singular rule.
            /// </summary>
            /// <param name="rule">The rule.</param>
            /// <param name="replacement">The replacement.</param>
            private static void AddSingularRule(string rule, string replacement)
            {
                _singulars.Add(new InflectorRule(rule, replacement));
            }

            /// <summary>
            /// Makes the plural.
            /// </summary>
            /// <param name="word">The word.</param>
            /// <returns></returns>
            public static string MakePlural(string word)
            {
                return ApplyRules(_plurals, word);
            }

            /// <summary>
            /// Makes the singular.
            /// </summary>
            /// <param name="word">The word.</param>
            /// <returns></returns>
            public static string MakeSingular(string word)
            {
                return ApplyRules(_singulars, word);
            }

            /// <summary>
            /// Applies the rules.
            /// </summary>
            /// <param name="rules">The rules.</param>
            /// <param name="word">The word.</param>
            /// <returns></returns>
            private static string ApplyRules(IList<InflectorRule> rules, string word)
            {
                string result = word;
                if (!_uncountables.Contains(word.ToLower()))
                {
                    for (int i = rules.Count - 1; i >= 0; i--)
                    {
                        string currentPass = rules[i].Apply(word);
                        if (currentPass != null)
                        {
                            result = currentPass;
                            break;
                        }
                    }
                }
                return result;
            }

            /// <summary>
            /// Converts the string to title case.
            /// </summary>
            /// <param name="word">The word.</param>
            /// <returns></returns>
            public static string ToTitleCase(string word)
            {
                return Regex.Replace(ToHumanCase(AddUnderscores(word)), @"\b([a-z])",
                    delegate(Match match) { return match.Captures[0].Value.ToUpper(); });
            }

            /// <summary>
            /// Converts the string to human case.
            /// </summary>
            /// <param name="lowercaseAndUnderscoredWord">The lowercase and underscored word.</param>
            /// <returns></returns>
            public static string ToHumanCase(string lowercaseAndUnderscoredWord)
            {
                return MakeInitialCaps(Regex.Replace(lowercaseAndUnderscoredWord, @"_", " "));
            }


            /// <summary>
            /// Adds the underscores.
            /// </summary>
            /// <param name="pascalCasedWord">The pascal cased word.</param>
            /// <returns></returns>
            public static string AddUnderscores(string pascalCasedWord)
            {
                return Regex.Replace(Regex.Replace(Regex.Replace(pascalCasedWord, @"([A-Z]+)([A-Z][a-z])", "$1_$2"), @"([a-z\d])([A-Z])", "$1_$2"), @"[-\s]", "_").ToLower();
            }

            /// <summary>
            /// Makes the initial caps.
            /// </summary>
            /// <param name="word">The word.</param>
            /// <returns></returns>
            public static string MakeInitialCaps(string word)
            {
                return String.Concat(word.Substring(0, 1).ToUpper(), word.Substring(1).ToLower());
            }

            /// <summary>
            /// Makes the initial lower case.
            /// </summary>
            /// <param name="word">The word.</param>
            /// <returns></returns>
            public static string MakeInitialLowerCase(string word)
            {
                return String.Concat(word.Substring(0, 1).ToLower(), word.Substring(1));
            }


            /// <summary>
            /// Determine whether the passed string is numeric, by attempting to parse it to a double
            /// </summary>
            /// <param name="str">The string to evaluated for numeric conversion</param>
            /// <returns>
            /// 	<c>true</c> if the string can be converted to a number; otherwise, <c>false</c>.
            /// </returns>
            public static bool IsStringNumeric(string str)
            {
                double result;
                return (double.TryParse(str, NumberStyles.Float, NumberFormatInfo.CurrentInfo, out result));
            }

            /// <summary>
            /// Adds the ordinal suffix.
            /// </summary>
            /// <param name="number">The number.</param>
            /// <returns></returns>
            public static string AddOrdinalSuffix(string number)
            {
                if (IsStringNumeric(number))
                {
                    int n = int.Parse(number);
                    int nMod100 = n % 100;

                    if (nMod100 >= 11 && nMod100 <= 13)
                        return String.Concat(number, "th");

                    switch (n % 10)
                    {
                        case 1:
                            return String.Concat(number, "st");
                        case 2:
                            return String.Concat(number, "nd");
                        case 3:
                            return String.Concat(number, "rd");
                        default:
                            return String.Concat(number, "th");
                    }
                }
                return number;
            }

            /// <summary>
            /// Converts the underscores to dashes.
            /// </summary>
            /// <param name="underscoredWord">The underscored word.</param>
            /// <returns></returns>
            public static string ConvertUnderscoresToDashes(string underscoredWord)
            {
                return underscoredWord.Replace('_', '-');
            }


            #region Nested type: InflectorRule

            /// <summary>
            /// Summary for the InflectorRule class
            /// </summary>
            private class InflectorRule
            {
                /// <summary>
                /// 
                /// </summary>
                public readonly Regex regex;

                /// <summary>
                /// 
                /// </summary>
                public readonly string replacement;

                /// <summary>
                /// Initializes a new instance of the <see cref="InflectorRule"/> class.
                /// </summary>
                /// <param name="regexPattern">The regex pattern.</param>
                /// <param name="replacementText">The replacement text.</param>
                public InflectorRule(string regexPattern, string replacementText)
                {
                    regex = new Regex(regexPattern, RegexOptions.IgnoreCase);
                    replacement = replacementText;
                }

                /// <summary>
                /// Applies the specified word.
                /// </summary>
                /// <param name="word">The word.</param>
                /// <returns></returns>
                public string Apply(string word)
                {
                    if (!regex.IsMatch(word))
                        return null;

                    string replace = regex.Replace(word, replacement);
                    if (word == word.ToUpper())
                        replace = replace.ToUpper();

                    return replace;
                }
            }

            #endregion

        }
    }
}
